package com.tjsinfo.tjsoa.baobiao.bo;

import java.util.List;
import java.util.Map;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;

import com.tjsinfo.tjsoa.Dao.IHibernateDao;
import com.tjsinfo.tjsoa.Dao.IQbcDao;
import com.tjsinfo.tjsoa.baobiao.bo.IHqJshtjbManage;
import com.tjsinfo.tjsoa.baobiao.vo.THqJshbfkhtjb;
import com.tjsinfo.tjsoa.system.dao.ISystemSpringDao;


/**
 * 用户BO
 * @author 天津市电子计算机研究所
 *
 */
	
public class THqJshtjbManage  implements IHqJshtjbManage{
	private IQbcDao qbcDao;
	private IHibernateDao hibernateDao;
	private ISystemSpringDao springDao;
	
	public ISystemSpringDao getSpringDao() {
		return springDao;
	}

	public void setSpringDao(ISystemSpringDao springDao) {
		this.springDao = springDao;
	}

	public IHibernateDao getHibernateDao() {
		return hibernateDao;
	}

	public void setHibernateDao(IHibernateDao hibernateDao) {
		this.hibernateDao = hibernateDao;
	}

	public IQbcDao getQbcDao() {
		return qbcDao;
	}

	public void setQbcDao(IQbcDao qbcDao) {
		this.qbcDao = qbcDao;
	}
	
	/**
	 * 根据年份季度、单位、性别获得统计表信息
	 * @param yue 年份季度
	 * @param czy 操作员
	 * @return
	 */
	public List<THqJshbfkhtjb> findByYueYh(String yue, Integer yhid, String xbbm ){
  
		DetachedCriteria criteria = DetachedCriteria.forClass(THqJshbfkhtjb.class); 
		criteria.add(Restrictions.eq("yue", yue));
		criteria.add(Restrictions.eq("yhid", yhid));
		criteria.add(Restrictions.eq("xbbm", xbbm));
		return  (List<THqJshbfkhtjb>) qbcDao.getCriteria(criteria);
	}
	
	/**
	 * 追加报表
	 * @param jshtjb
	 */
	public void addBaobiao(THqJshbfkhtjb jshtjb1,THqJshbfkhtjb jshtjb2){
		hibernateDao.save(jshtjb1);
		hibernateDao.save(jshtjb2);
	}

	/**
	 * 更新报表
	 * @param jshtjb
	 */
	public void updateBaobiao(THqJshbfkhtjb jshtjb1,THqJshbfkhtjb jshtjb2){
		hibernateDao.update(jshtjb1);
		hibernateDao.update(jshtjb2);
	}
	
	/**
	 * 查找已存在的报表
	 * @param id 用户id
	 * @return List<THqJshbfkhtjb>
	 */
	public List<THqJshbfkhtjb> findByYh(Integer yhid) {
		// TODO Auto-generated method stub
		DetachedCriteria criteria = DetachedCriteria.forClass(THqJshbfkhtjb.class); 
		criteria.add(Restrictions.eq("yhid", yhid));
		criteria.add(Restrictions.eq("xbbm", "1"));
		return  (List<THqJshbfkhtjb>) qbcDao.getCriteria(criteria);
	}
	/**
	 * 根据季度查找已提交的报表
	 * @param yueString
	 */
	public List<THqJshbfkhtjb> findByJIdu(String yueString) {
		// TODO Auto-generated method stub
		DetachedCriteria criteria = DetachedCriteria.forClass(THqJshbfkhtjb.class); 
		criteria.add(Restrictions.eq("yue", yueString));
		criteria.add(Restrictions.eq("xbbm", "1"));
		criteria.add(Restrictions.eq("state", "1"));
		return  (List<THqJshbfkhtjb>) qbcDao.getCriteria(criteria);
	}

	/**
	 * 查询存在已提交报表的季度
	 */
	public List<THqJshbfkhtjb> findByState() {
		// TODO Auto-generated method stub
		
		return null;
	}

	/**
	 * 根据年份季度、单位、是否已提交获得统计表信息
	 * @param yue 年份季度
	 * @param czy 操作员
	 * @return
	 */
	public List<THqJshbfkhtjb> findByYueYh(String yue, Integer yhid,
			String xbbm, String state) {
		// TODO Auto-generated method stub
		DetachedCriteria criteria = DetachedCriteria.forClass(THqJshbfkhtjb.class); 
		criteria.add(Restrictions.eq("yue", yue));
		criteria.add(Restrictions.eq("yhid", yhid));
		criteria.add(Restrictions.eq("xbbm", xbbm));
		criteria.add(Restrictions.eq("state", state));		
		return  (List<THqJshbfkhtjb>) qbcDao.getCriteria(criteria);
	}

	/**
	 * 根据id查找统计表
	 * @param tjbid1
	 * @return
	 */
	public THqJshbfkhtjb findById(Integer id) {
		// TODO Auto-generated method stub
		DetachedCriteria criteria = DetachedCriteria.forClass(THqJshbfkhtjb.class); 
		criteria.add(Restrictions.eq("id", id));
		return (THqJshbfkhtjb)qbcDao.getCriteria(criteria).get(0);
	}

	/**
	 * 生成包含上报统计表的季度列表
	 */
	public List<Map<String, String>> getTjJidu() {
		// TODO Auto-generated method stub
		List<Map<String, String>> tempList = springDao.commonSelect(new String[]{"yue"}, "SELECT yue FROM t_hq_jshbfkhtjb WHERE state = '1' GROUP BY yue ORDER BY yue DESC");
		return tempList;
	}
	
	/**
	 * 汇总统计数据
	 * @param yueString
	 * @param string
	 * @return
	 */
	public THqJshbfkhtjb collectByYue(String yueString, String xbbm) {
		// TODO Auto-generated method stub
		
		String[] fieldsStrings = new String[]{"yue","xbbm","s_zhgzs","s_whqn","s_wshynl","s_jy","s_fh","s_yzh","s_yp","s_kfy","s_gj","s_ym","s_qt","s_brqagyn","s_hy_yt","s_hy_et","s_hy_jhw","s_xh","s_chsh_yt","s_chsh_et","s_chsh_jhw","s_ytsh","s_lzhsh","s_cjzbdy","s_dxrglch","s_chxrglch","s_bzzsh","s_qybz","s_ypxrsh","s_shjpxrsh","s_xhdj25","s_xhdj23","s_xhdj22","s_shyrsh","s_wyrsh"};
		StringBuffer SQlBuffer = new StringBuffer(); 
			SQlBuffer.append("SELECT yue, xbbm, sum(zhgzs)as s_zhgzs, sum(whqn) as s_whqn, sum(wshynl) as s_wshynl, sum(jy) as s_jy, sum(fh) as s_fh,");
			SQlBuffer.append(" sum(yzh) as s_yzh, sum(yp) as s_yp, sum(kfy) as s_kfy, sum(gj) as s_gj, sum(ym) as s_ym, sum(qt) as s_qt, sum(brqagyn) as s_brqagyn,"); 
			SQlBuffer.append(" sum(hy_yt) as s_hy_yt, sum(hy_et) as s_hy_et, sum(hy_jhw) as s_hy_jhw, sum(xh) as s_xh, sum(chsh_yt) as s_chsh_yt, sum(chsh_et) as s_chsh_et,"); 
			SQlBuffer.append(" sum(chsh_jhw) as s_chsh_jhw, sum(ytsh) as s_ytsh, sum(lzhsh) as s_lzhsh, sum(cjzbdy) as s_cjzbdy, sum(dxrglch) as s_dxrglch, sum(chxrglch) as s_chxrglch,");
			SQlBuffer.append(" sum(bzzsh) as s_bzzsh, sum(qybz) as s_qybz, sum(ypxrsh) as s_ypxrsh, sum(shjpxrsh) as s_shjpxrsh, sum(xhdj25) as s_xhdj25, sum(xhdj23) as s_xhdj23,");
			SQlBuffer.append(" sum(xhdj22) as s_xhdj22, sum(shyrsh) as s_shyrsh, sum(wyrsh) as s_wyrsh FROM  t_hq_jshbfkhtjb WHERE state = '1' AND yue = '");
			SQlBuffer.append(yueString);
			SQlBuffer.append("' AND xbbm = '");
			SQlBuffer.append(xbbm);
			SQlBuffer.append("' GROUP BY yue, xbbm");
		Map<String, String> map = springDao.commonSelect(fieldsStrings, SQlBuffer.toString()).get(0);
		THqJshbfkhtjb tjbtemp = new THqJshbfkhtjb(map.get("yue"),map.get("xbbm"),map.get("s_zhgzs"),map.get("s_whqn"),map.get("s_wshynl"),map.get("s_jy"),map.get("s_fh"),map.get("s_yzh"),map.get("s_yp"),map.get("s_kfy"),map.get("s_gj"),map.get("s_ym"),map.get("s_qt"),map.get("s_brqagyn"),map.get("s_hy_yt"),map.get("s_hy_et"),map.get("s_hy_jhw"),map.get("s_xh"),map.get("s_chsh_yt"),map.get("s_chsh_et"),map.get("s_chsh_jhw"),map.get("s_ytsh"),map.get("s_lzhsh"),map.get("s_cjzbdy"),map.get("s_dxrglch"),map.get("s_chxrglch"),map.get("s_bzzsh"),map.get("s_qybz"),map.get("s_ypxrsh"),map.get("s_shjpxrsh"),map.get("s_xhdj25"),map.get("s_xhdj23"),map.get("s_xhdj22"),map.get("s_shyrsh"),map.get("s_wyrsh"));
		return tjbtemp;
	}

	/**
	 * 按时间段统计上报及时率
	 * @param startmonth
	 * @param endmonth
	 * @return
	 */
	public List<Map<String, String>> countIntime(String beginmonth,
			String endmonth) {
		// TODO Auto-generated method stub
		String[] fieldsStrings = new String[]{"yhid","intimecount"};
		String sQLString = "SELECT  yhid, COUNT(*) AS intimecount FROM t_hq_jshbfkhtjb  WHERE yue >='"+beginmonth + "'AND yue <='"+ endmonth +"' AND intime = '1' AND xbbm='1' GROUP BY yhid";
		List<Map<String, String>> maplist = springDao.commonSelect(fieldsStrings, sQLString);
		return maplist;
	}

	/**
	 * 按年度统计上报及时率
	 * @param startmonth
	 * @param endmonth
	 * @return
	 */
	public List<Map<String, String>> countIntime(String yearString) {
		// TODO Auto-generated method stub
		
		String[] fieldsStrings = new String[]{"yhid","intimecount"};
		String sQLString = "SELECT  yhid, COUNT(*) AS intimecount FROM t_hq_jshbfkhtjb WHERE yue LIKE '"+yearString + "%' AND intime = '1'  GROUP BY yhid";
		List<Map<String, String>> maplist = springDao.commonSelect(fieldsStrings, sQLString);
		return maplist;
	}


}
